﻿using System;
using System.Collections.Generic;
using System.Collections.ObjectModel;
using System.Data;
using System.Data.SqlServerCe;
using System.IO;
using System.Reflection;
using Samurai.Infrastructure;

using System.Linq;
using System.Configuration;

namespace Samurai.DAL
{
    public class Manager
    {
        private static Manager _instance;

        public static Manager Instance
        {
            get
            {
                if (_instance == null)
                {
                    _instance = new Manager();
                }
                return _instance;
            }
        }

        private Manager()
        {

        }

        private static SqlCeConnection GetSQLConnection()
        {
            var connectionString = ConfigurationManager.ConnectionStrings["Samurai.Client.Properties.Settings.SamuraiDBConnectionString"].ConnectionString;

            return new SqlCeConnection(connectionString);
        }

        private static SqlCeCommand GetSQLCommand(SqlCeConnection connection)
        {
            return new SqlCeCommand { Connection = connection };
        }

        public void ConnectListAndSaveSQLCompactExample()
        {
            var connection = GetSQLConnection();
            connection.Open();

            const string sql = @"INSERT INTO test_table (col1) VALUES ('aaa')";

            var cmd = GetSQLCommand(connection);

            cmd.CommandText = sql;

            cmd.ExecuteScalar();
            connection.Close();
        }

        public Repository[] GetRepositoryLocations()
        {
            var connection = GetSQLConnection();

            var cmd = new SqlCeCommand { Connection = connection };

            var repoLocations = new List<Repository>();

            connection.Open();

            cmd.CommandText = "SELECT * FROM Repository";

            var reader = cmd.ExecuteReader();

            while (reader.Read())
            {
                var location = (string)reader["Location"];

                repoLocations.Add(new Repository(location));
            }

            connection.Close();

            return repoLocations.ToArray();
        }

        public void AddCandidateMovies(List<CandidateMovie> candidateMovies)
        {
            var connection = GetSQLConnection();

            connection.Open();

            foreach (var movie in candidateMovies)
            {
                const string sql1 = "SELECT * FROM CandidateMovie WHERE Path = @path";

                var cmd = GetSQLCommand(connection);

                cmd.CommandText = sql1;
                cmd.Parameters.Clear();
                cmd.Parameters.AddWithValue("@path", movie.Path);
                var response = cmd.ExecuteScalar();

                if (response == null)
                {
                    var sql = string.Format(@"INSERT INTO CandidateMovie (Title, Path) VALUES (@name, @path)");
                    cmd.CommandText = sql;
                    cmd.Parameters.Clear();
                    cmd.Parameters.AddWithValue("@name", movie.Name);
                    cmd.Parameters.AddWithValue("@path", movie.Path);
                    cmd.ExecuteScalar();
                }
            }
            connection.Close();
        }

        public CandidateMovie[] GetCandidateMovies()
        {
            var candidateMovies = new List<CandidateMovie>();

            var connection = GetSQLConnection();

            connection.Open();

            const string sql = "SELECT * FROM CandidateMovie";

            var cmd = GetSQLCommand(connection);

            cmd.CommandText = sql;

            var reader = cmd.ExecuteReader();

            while (reader.Read())
            {
                var cm = new CandidateMovie();

                var titleData = reader["Title"];
                cm.Name = titleData == null ? string.Empty : titleData.ToString();

                var pathData = reader["Path"];
                cm.Path = pathData == null ? string.Empty : pathData.ToString();

                candidateMovies.Add(cm);
            }

            connection.Close();

            return candidateMovies.ToArray();
        }

        public bool Exists(string movieLocation)
        {
            var connection = GetSQLConnection();

            connection.Open();            

            var sql = string.Format("SELECT * FROM Movie WHERE Path=@path");

            var cmd = GetSQLCommand(connection);
            cmd.Parameters.AddWithValue("@path", movieLocation);

            cmd.CommandText = sql;

            var response = cmd.ExecuteScalar();

            connection.Close();

            return response != null;
        }

        public void AddMovie(Movie movie)
        {
            var connection = GetSQLConnection();

            try
            {
                connection.Open();

                var sqlQuery = string.Format("SELECT count(*) FROM Movie WHERE Path=@path");

                var cmd = GetSQLCommand(connection);
                cmd.Parameters.AddWithValue("@path", movie.Path);

                cmd.CommandText = sqlQuery;

                var response = cmd.ExecuteScalar();

                if ((int)response == 0)
                {
                    var sqlAddMovie =
                        string.Format(
                            @"INSERT INTO Movie (Title, Year, Rating, Url, Path, Error, ImageUrl, Image) VALUES (@title, @year, @rating, @url, @path, @error, @imageUrl, @image)");

                    cmd = GetSQLCommand(connection);

                    cmd.CommandText = sqlAddMovie;

                    cmd.Parameters.Clear();

                    cmd.Parameters.AddWithValue("@title", movie.Title ?? string.Empty);
                    cmd.Parameters.AddWithValue("@year", movie.Year ?? string.Empty);
                    cmd.Parameters.AddWithValue("@rating", movie.Rating ?? string.Empty);
                    cmd.Parameters.AddWithValue("@url", movie.Url ?? string.Empty);
                    cmd.Parameters.AddWithValue("@path", movie.Path ?? string.Empty);
                    cmd.Parameters.AddWithValue("@error", movie.Error ?? string.Empty);
                    cmd.Parameters.AddWithValue("@imageUrl", movie.ImageUrl ?? string.Empty);
                    cmd.Parameters.AddWithValue("@Image", movie.Image ?? new byte[0]);

                    cmd.ExecuteNonQuery();

                    //Add genre info

                    foreach (var genreTitle in movie.Genres)
                    {
                        //genre table
                        const string sqlGenreCount = @"SELECT COUNT(ID) FROM Genre WHERE (Title = @title)";
                        cmd.CommandText = sqlGenreCount;
                        cmd.Parameters.Clear();
                        cmd.Parameters.AddWithValue("@title", genreTitle);

                        var genreCountData = cmd.ExecuteScalar();
                        var genreCount = (int)genreCountData;

                        if (genreCount == 0)
                        {
                            const string sqlAddGenre = @"INSERT INTO Genre (Title) VALUES (@title)";
                            cmd.CommandText = sqlAddGenre;
                            cmd.Parameters.Clear();
                            cmd.Parameters.AddWithValue("@title", genreTitle);

                            cmd.ExecuteScalar();
                        }


                        //movieID
                        const string sqlGetMovieID = "SELECT ID FROM Movie WHERE Path=@path";
                        cmd.CommandText = sqlGetMovieID;
                        cmd.Parameters.Clear();
                        cmd.Parameters.AddWithValue("@path", movie.Path);
                        var movieIDData = cmd.ExecuteScalar();

                        //genreID
                        const string sqlGetGenreID = "SELECT ID FROM Genre WHERE Title=@title";
                        cmd.CommandText = sqlGetGenreID;
                        cmd.Parameters.Clear();
                        cmd.Parameters.AddWithValue("@title", genreTitle);
                        var genreIDData = cmd.ExecuteScalar();



                        //movie-genre table

                        var movieID = (int)movieIDData;
                        var genreID = (int)genreIDData;

                        const string sqlAddMovieGenre = @"INSERT INTO MovieGenre (MovieID, GenreID) VALUES (@movieID, @genreID)";
                        cmd.CommandText = sqlAddMovieGenre;
                        cmd.Parameters.Clear();
                        cmd.Parameters.AddWithValue("@movieID", movieID);
                        cmd.Parameters.AddWithValue("@genreID", genreID);

                        cmd.ExecuteScalar();
                    }
                }
            }

            finally
            {
                if (connection.State == ConnectionState.Open)
                    connection.Close();
            }


        }

        public IEnumerable<Movie> GetValidMovies()
        {
            var candidateMovies = new List<Movie>();

            var connection = GetSQLConnection();

            const string sqlMovieGenreInformation =
                @"SELECT Movie.ID, Movie.Title, Genre.ID AS GenreID, Genre.Title AS GenreTitle
                  FROM Movie INNER JOIN
                  MovieGenre ON Movie.ID = MovieGenre.MovieID INNER JOIN
                  Genre ON MovieGenre.GenreID = Genre.ID
                  WHERE (Movie.ID = @movieID)";

            connection.Open();

            const string sql = ("SELECT * FROM Movie");

            var cmd = GetSQLCommand(connection);
            cmd.CommandText = sql;

            var reader = cmd.ExecuteReader();

            while (reader.Read())
            {
                var movie = new Movie();

                var errorData = reader["Error"];

                if (string.IsNullOrEmpty(errorData.ToString()))
                {
                    movie.ID = (int)reader["ID"];

                    var titleData = reader["Title"];
                    movie.Title = titleData == null ? string.Empty : titleData.ToString();

                    var pathData = reader["Path"];
                    movie.Path = pathData == null ? string.Empty : pathData.ToString();

                    var yearData = reader["Year"];
                    movie.Year = yearData == null ? string.Empty : yearData.ToString();

                    var ratingData = reader["Rating"];
                    movie.Rating = ratingData == null ? string.Empty : ratingData.ToString();

                    var urlData = reader["Url"];
                    movie.Url = urlData == null ? string.Empty : urlData.ToString();

                    var imageUrlData = reader["ImageUrl"];
                    movie.ImageUrl = imageUrlData == null ? string.Empty : imageUrlData.ToString();

                    var imageData = reader["Image"];
                    movie.Image = imageData == null ? new byte[0] : (byte[])imageData;

                    cmd.CommandText = sqlMovieGenreInformation;
                    cmd.Parameters.Clear();
                    cmd.Parameters.AddWithValue("@movieID", movie.ID);
                    var genreReader = cmd.ExecuteReader();

                    var genreList = new List<string>();
                    while (genreReader.Read())
                    {
                        var genreData = genreReader["GenreTitle"];
                        genreList.Add((string)genreData);
                    }
                    movie.Genres = genreList.ToArray();

                    candidateMovies.Add(movie);
                }
            }

            connection.Close();

            return candidateMovies.ToArray();
        }

        public IEnumerable<Movie> GetNonMovies()
        {
            var candidateMovies = new List<Movie>();

            var connection = GetSQLConnection();

            connection.Open();

            const string sql = ("SELECT * FROM Movie");

            var cmd = GetSQLCommand(connection);

            cmd.CommandText = sql;

            var reader = cmd.ExecuteReader();

            while (reader.Read())
            {
                var cm = new Movie();

                var errorData = reader["Error"];

                if (errorData.ToString().Equals(Constants.ErrorFilmNotFound, StringComparison.InvariantCultureIgnoreCase))
                {
                    cm.ID = (int)reader["ID"];

                    var pathData = reader["Path"];
                    cm.Path = pathData == null ? string.Empty : pathData.ToString();

                    cmd.CommandText = string.Format("SELECT Title FROM CandidateMovie WHERE Path='{0}'", cm.Path);
                    var titleData = cmd.ExecuteScalar();

                    //var titleData = reader["Title"];
                    cm.Title = titleData == null ? string.Empty : titleData.ToString();

                    candidateMovies.Add(cm);
                }
            }

            connection.Close();

            return candidateMovies.ToArray();
        }

        public void MarkNotAMovie(Movie movieData)
        {
            string sql = string.Format("UPDATE Movie SET Error='{0}' WHERE Path='{1}'", Constants.InfoNotAMovie, movieData.Path);

            var connection = GetSQLConnection();
            var cmd = GetSQLCommand(connection);

            connection.Open();

            cmd.CommandText = sql;
            connection.Close();
        }

        public void UpdateMovie(Movie movieData, Movie newMovieData)
        {
            var connection = GetSQLConnection();
            var cmd = GetSQLCommand(connection);

            connection.Open();

            string sql = string.Format(
                @"UPDATE Movie 
                    SET Error='{0}', Title='{1}', Year='{2}', Rating='{3}', Url='{4}'
                    WHERE Path='{5}'",
                                     newMovieData.Error,
                                     newMovieData.Title,
                                     newMovieData.Year,
                                     newMovieData.Rating,
                                     newMovieData.Url,
                                     movieData.Path);
            cmd.CommandText = sql;


            connection.Close();
        }

        public IEnumerable<Movie> FindMovieByTitle(string searchTitle)
        {
            var candidateMovies = new List<Movie>();

            var connection = GetSQLConnection();
            var cmd = GetSQLCommand(connection);

            connection.Open();

            string sql = string.Format("SELECT * FROM Movie WHERE Title LIKE '%{0}%'", searchTitle);
            cmd.CommandText = sql;

            var reader = cmd.ExecuteReader();

            while (reader.Read())
            {
                var cm = new Movie();

                var errorData = reader["Error"];

                if (string.IsNullOrEmpty(errorData.ToString()))
                {
                    cm.ID = (int)reader["ID"];

                    var titleData = reader["Title"];
                    cm.Title = titleData == null ? string.Empty : titleData.ToString();

                    var pathData = reader["Path"];
                    cm.Path = pathData == null ? string.Empty : pathData.ToString();

                    var yearData = reader["Year"];
                    cm.Year = yearData == null ? string.Empty : yearData.ToString();

                    var ratingData = reader["Rating"];
                    cm.Rating = ratingData == null ? string.Empty : ratingData.ToString();

                    var urlData = reader["Url"];
                    cm.Url = urlData == null ? string.Empty : urlData.ToString();

                    var imageUrlData = reader["ImageUrl"];
                    cm.ImageUrl = imageUrlData == null ? string.Empty : imageUrlData.ToString();

                    candidateMovies.Add(cm);
                }
            }

            connection.Close();

            return candidateMovies.ToArray();
        }

        public Repository AddRepository(string location)
        {
            var connection = GetSQLConnection();
            var cmd = GetSQLCommand(connection);

            connection.Open();

            string sql = string.Format(@"INSERT INTO Repository (Location) VALUES (@location)");

            cmd.CommandText = sql;

            cmd.Parameters.AddWithValue("@location", location);

            cmd.ExecuteNonQuery();

            connection.Close();

            return new Repository(location);
        }

        public void RemoveFromRepository(Repository repository)
        {
            var connection = GetSQLConnection();
            var cmd = GetSQLCommand(connection);

            connection.Open();

            const string sql = "DELETE FROM Repository WHERE Location=@location";

            cmd.CommandText = sql;
            cmd.Parameters.Clear();
            cmd.Parameters.AddWithValue("@location", repository.Location);

            cmd.ExecuteNonQuery();

            connection.Close();
        }

        public CandidateMovie[] GetNewCandidates(IEnumerable<CandidateMovie> directories)
        {
            var newMovies = new List<CandidateMovie>();

            var currentDirectoryLocations = directories.Select(cm => cm.Path);

            var connection = GetSQLConnection();
            var cmd = GetSQLCommand(connection);

            connection.Open();

            const string sql = "SELECT * FROM CandidateMovie";

            cmd.CommandText = sql;

            var reader = cmd.ExecuteReader();

            while (reader.Read())
            {
                var path = (string)reader["Path"];
                if (!currentDirectoryLocations.Contains(path))
                {
                    var title = (string)reader["Title"];
                    var newMovie = new CandidateMovie
                                       {
                                           Name = title,
                                           Path = path
                                       };

                    newMovies.Add(newMovie);
                }

            }

            connection.Close();

            return newMovies.ToArray();
        }

        public Movie[] GetNewValidMovies(IEnumerable<Movie> movies)
        {
            var newMovies = new List<Movie>();

            var currentDirectoryLocations = movies.Select(cm => cm.Path);

            var connection = GetSQLConnection();
            var cmd = GetSQLCommand(connection);

            connection.Open();

            const string sql = "SELECT * FROM Movie";

            const string sqlMovieGenreInformation =
                @"SELECT Movie.ID, Movie.Title, Genre.ID AS GenreID, Genre.Title AS GenreTitle
                  FROM Movie INNER JOIN
                  MovieGenre ON Movie.ID = MovieGenre.MovieID INNER JOIN
                  Genre ON MovieGenre.GenreID = Genre.ID
                  WHERE (Movie.ID = @movieID)";

            cmd.CommandText = sql;

            using (var reader = cmd.ExecuteReader())
            {
                while (reader.Read())
                {
                    var path = (string)reader["Path"];

                    var errorData = reader["Error"];

                    if (!currentDirectoryLocations.Contains(path) && string.IsNullOrEmpty(errorData.ToString()))
                    {
                        var title = (string)reader["Title"];
                        var year = (string)reader["Year"];
                        var rating = (string)reader["Rating"];
                        var url = (string)reader["Url"];
                        var imageUrl = (string)reader["ImageUrl"];

                        var imageData = reader["Image"];
                        var image = imageData == null ? new byte[0] : (byte[])imageData;

                        var newMovie = new Movie
                                           {
                                               Title = title,
                                               Path = path,
                                               Year = year,
                                               Rating = rating,
                                               Url = url,
                                               ImageUrl = imageUrl,
                                               Image = image
                                           };


                        cmd.CommandText = sqlMovieGenreInformation;
                        cmd.Parameters.Clear();
                        var movieID = (int)reader["ID"];
                        cmd.Parameters.AddWithValue("@movieID", movieID);

                        var genreList = new List<string>();
                        using (var genreReader = cmd.ExecuteReader())
                        {
                            while (genreReader.Read())
                            {
                                genreList.Add((string)genreReader["GenreTitle"]);
                            }
                        }

                        newMovie.Genres = genreList.ToArray();
                        newMovies.Add(newMovie);
                    }

                }
            }
            connection.Close();

            return newMovies.ToArray();
        }

        public Repository[] GetNewRepositories(Repository[] currentRepositories)
        {
            var newRepositories = new List<Repository>();

            var currentRepositoryLocations = currentRepositories.Select(repo => repo.Location);

            var connection = GetSQLConnection();
            var cmd = GetSQLCommand(connection);

            connection.Open();

            const string sql = "SELECT * FROM Repository";

            cmd.CommandText = sql;

            var reader = cmd.ExecuteReader();

            while (reader.Read())
            {
                var location = (string)reader["Location"];

                if (!currentRepositoryLocations.Contains(location))
                {
                    var newRepository = new Repository(location);

                    newRepositories.Add(newRepository);
                }

            }

            connection.Close();

            return newRepositories.ToArray();
        }

        public string[] GetNewGenres(IEnumerable<string> existingGenres)
        {
            var genres = new List<string>();

            var connection = GetSQLConnection();

            try
            {
                connection.Open();

                var command = GetSQLCommand(connection);

                const string sqlGetGenres = "SELECT Title FROM Genre";

                command.CommandText = sqlGetGenres;

                using (var reader = command.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        var genreTitle = (string)reader["Title"];

                        if (!existingGenres.Contains(genreTitle))
                            genres.Add(genreTitle);
                    }
                }

            }
            finally
            {
                if (connection.State == ConnectionState.Open)
                    connection.Close();
            }



            return genres.ToArray();
        }

        public IEnumerable<Movie> GetMoviesByGenre(string genreTitle)
        {
            var movies = new List<Movie>();

            const string sqlGetMovieByGenre =
                @"SELECT Movie.ID, Movie.Title, Movie.Year, Movie.Rating, Movie.Url, Movie.Path, Movie.ImageUrl, Movie.Error, Movie.Image
                  FROM Movie INNER JOIN
                  MovieGenre ON Movie.ID = MovieGenre.MovieID INNER JOIN
                  Genre ON MovieGenre.GenreID = Genre.ID
                  WHERE (Genre.Title = @title)";


            var connection = GetSQLConnection();

            try
            {
                connection.Open();

                var command = GetSQLCommand(connection);

                command.CommandText = sqlGetMovieByGenre;
                command.Parameters.Clear();
                command.Parameters.AddWithValue("@title", genreTitle);

                using (var reader = command.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        var movie = new Movie();
                        movie.Error = reader["Error"] == null ? string.Empty : (string)reader["Error"];
                        movie.ID = (int)reader["ID"];
                        movie.ImageUrl = reader["ImageUrl"] == null ? string.Empty : (string)reader["ImageUrl"];
                        movie.Path = reader["Path"] == null ? string.Empty : (string)reader["Path"];
                        movie.Rating = reader["Rating"] == null ? string.Empty : (string)reader["Rating"];
                        movie.Title = reader["Title"] == null ? string.Empty : (string)reader["Title"];
                        movie.Url = reader["Url"] == null ? string.Empty : (string)reader["Url"];
                        movie.Year = reader["Year"] == null ? string.Empty : (string)reader["Year"];
                        movie.Image = reader["Image"] == null ? new byte[0] : (byte[])reader["Image"];

                        movies.Add(movie);
                    }
                }
            }
            finally
            {
                if (connection.State == ConnectionState.Open)
                    connection.Close();
            }

            return movies;
        }
    }
}
